Release 10.1A: OpenEdge Reporting:
Deploying Crystal Reports

Understanding transactions and isolation levels

SQL defines isolation levels in terms of the inconsistencies they allow:

Table 1–1 identifies which inconsistencies are either permitted or prevented by each isolation level.

Table 1–1: Transaction isolation levels

Isolation	Dirty read	Nonrepeatable read	Phantom read
READ UNCOMMITTED	Permitted	Permitted	Permitted
READ COMMITTED	Prevented	Permitted	Permitted
REPEATABLE READ	Prevented	Prevented	Permitted
SERIALIZABLE	Prevented	Prevented	Prevented

Dirty read

A Dirty read occurs when one user is updating or inserting a record. At the same time, a different user is also reading it, but has not yet committed any work to the database.

Nonrepeatable read

A Nonrepeatable read occurs when one user is repeating a read operation on the same records but has updated values.

Phantom read

A Phantom read occurs when one user is repeating a read operation on the same records, but has new records in the results set:

READ UNCOMMITTED

Also called a Dirty read. When this isolation level is used, a transaction can read uncommitted data that later may be rolled back. A transaction that uses this isolation level can only fetch data but can’t update, delete, or insert data.

READ COMMITTED

With this isolation level, Dirty reads are not possible, but if the same row is read repeatedly during the same transaction, its contents may be changed or the entire row may be deleted by other transactions.

REPEATABLE READ

This isolation level guarantees that a transaction can read the same row many times and it will remain intact. However, if a query with the same search criteria (the same WHERE clause) is executed more than once, each execution may return different set of rows. This may happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria.

SERIALIZABLE

This isolation level guarantees that none of the above happens. In addition, it guarantees that transactions that use this level will be completely isolated from other transactions.

Based on this information, we can provide basic guidelines for choosing the proper isolation level for the ODBC connection that is going to be used by Crystal Reports:

READ UNCOMMITTED should be used with reports that do not rely on data accuracy. Usually these same reports also process/access a high number of records. This optimizes performance while executing your report with a minimum number of database locks. Examples of reports in this category:

Statistical information at the end of a month.
Sales report covering a previous year.
Reports running daily that accesses data which is rarely updated.
Reports running daily if the values displayed are only used as indicators.

COMMITTED READ should be used with reports running daily on data that is frequently modified. This enables good performance while executing reports on a “live” database with an average number of record locks that are immediately released. Examples of reports in this category include:

Daily reports on regularly updated data requiring 100 percent accuracy at the time the report is processed.
Reports that provide snapshots of an operation at any time during the day. Such reports are used for monitoring purposes, such as stock exchange status reports.

REPEATABLE READ and SERIALIZABLE should not be used with reports as they do not add value at the time the report is generated, especially when compared to COMMITTED READ.

Having reviewed the transaction isolation levels, you can now configure your ODBC driver.